package com.dong.dao.Provider;

import com.dong.dao.BaseDao;
import com.dong.pojo.Provider;
import com.dong.pojo.User;
import com.microsoft.sqlserver.jdbc.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao{
    //增加供应商
    public int add(Connection connection, Provider provider) throws SQLException {
        PreparedStatement pstm = null;
        int updateRows = 0;
        String sql = "insert into smbms_provider (proCode,proName,proDesc," +
                "proContact,proPhone,proAddress,proFax,createdBy,creationDate) " +
                "values(?,?,?,?,?,?,?,?,?)";
        if(connection != null){
            Object[] params = {provider.getProCode(),provider.getProName(),provider.getProDesc(),
            provider.getProContact(),provider.getProPhone(),provider.getProAddress(),provider.getProFax(),
            provider.getCreatedBy(),provider.getCreationDate()};
            updateRows = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRows;
    }

    public List<Provider> getProviderList(Connection connection, String proName, String proid) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<Provider>();
        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider where 1=1 ");
            List<Object> list = new ArrayList<Object>();
            if(!StringUtils.isEmpty(proName)){
                sql.append(" and proName like ?");
                list.add("%"+proName+"%");
            }
            if(!StringUtils.isEmpty(proid)){
                sql.append(" and id like ?");
                list.add(proid);
            }
            Object[] params = list.toArray();
            System.out.println("sql ----> " + sql.toString());
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            while(rs.next()){
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProCode(rs.getString("proCode"));
                _provider.setProName(rs.getString("proName"));
                _provider.setProDesc(rs.getString("proDesc"));
                _provider.setProContact(rs.getString("proContact"));
                _provider.setProPhone(rs.getString("proPhone"));
                _provider.setProAddress(rs.getString("proAddress"));
                _provider.setProFax(rs.getString("proFax"));
                _provider.setCreationDate(rs.getTimestamp("creationDate"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null, pstm, rs);
        }
        return providerList;
    }

    //通过供应商名称、编码获取供应商列表
    public List<Provider> getProviderList(Connection connection, String proName, String proCode,int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        List<Provider> providerList = new ArrayList<Provider>();
        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select * from smbms_provider where 1=1 ");
            List<Object> list = new ArrayList<Object>();
            if(proName != null){
                sql.append(" and proName like ?");
                list.add("%" + proName + "%");
            }
            if(proCode != null){
                sql.append(" and proCode like ? ");
                list.add("%" + proCode + "%");
            }

            sql.append(" order by creationDate DESC offset ? rows fetch next ? rows only");
            currentPageNo = (currentPageNo-1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
            Object[] params = list.toArray();
            System.out.println("sql--------->"+sql.toString());
            rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);
            while(rs.next()){
                Provider _provider = new Provider();
                _provider.setId(rs.getInt("id"));
                _provider.setProCode(rs.getString("proCode"));
                _provider.setProName(rs.getString("proName"));
                _provider.setProDesc(rs.getString("proDesc"));
                _provider.setProContact(rs.getString("proContact"));
                _provider.setProPhone(rs.getString("proPhone"));
                _provider.setProAddress(rs.getString("proAddress"));
                _provider.setProFax(rs.getString("proFax"));
                _provider.setCreationDate(rs.getTimestamp("creationDate"));
                providerList.add(_provider);
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return providerList;
    }

    public Provider getproviderByid(Connection connection, String id) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        Provider provider = null;
        if(connection != null){
            String sql ="select * from smbms_provider where id=?";
            Object[] params = {id};
            rs = BaseDao.execute(connection, pstm, rs, sql, params);
            if(rs.next()){
                provider=new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreatedBy(rs.getInt("createdBy"));
                provider.setCreationDate(rs.getTimestamp("creationDate"));
                provider.setModifyBy(rs.getInt("modifyBy"));
                provider.setModifyDate(rs.getTimestamp("modifyDate"));
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return provider;
    }

    public int modifyProvider(Connection connection, Provider provider) throws SQLException {
        int flag = 0;
        PreparedStatement pstm = null;
        if(connection != null){
            String sql = "update smbms_provider set proName=?,proDesc=?,proContact=?,proPhone=?,proAddress=?,proFax=?,modifyDate=?,modifyBy=? where  id=?";
            Object[] params = {provider.getProName(),provider.getProDesc(),provider.getProContact(),
                                provider.getProPhone(),provider.getProAddress(),provider.getProFax(),
                                provider.getModifyDate(),provider.getModifyBy(),provider.getId()};
            flag = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null,pstm,null);
        }
        return flag;
    }

    public int deleteProviderById(Connection connection, String delId) throws Exception {
        int updateRow = 0;
        PreparedStatement pstm = null;
        if(connection != null){
            String sql = "delete from smbms_provider where id=?";
            Object[] params = {delId};
            updateRow = BaseDao.execute(connection, pstm, sql, params);
            BaseDao.closeResource(null,pstm,null);
        }
        return updateRow;
    }

    public int getProviderCount(Connection connection, String proName, String proCode) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int count = 0;
        if(connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select count(*) as count from smbms_provider");
            List<Object> list = new ArrayList<Object>();
            /*if(proName != null){
                sql.append(" and proName like ?");
                list.add("%" + proName +"%");
            }
            if(proCode != null){
                sql.append(" and proCode like ?");
                list.add("%" +proCode+"%");
            }*/

            //将list数据转换数组格式
            Object[] params = list.toArray();
            System.out.println("UserDaoImpl---->getUserCount:" + sql.toString());


            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            if(rs.next()){
                count = rs.getInt("count");
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return count;
    }
}
